﻿
using Dapper;
using isky.model;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace isky.dapper
{
    public class QuoteComparsionDal
    {
        //string productId, string areaId, DateTime? startDate, DateTime? endDate
       private string connectionString=@"Data Source= CRM-DB;Initial Catalog=crm_MSCRM;Persist Security Info=True;User ID=sa;Password=pass@word1";
        public IEnumerable<dynamic> GetQuoteData(string areaId, string productId,  string startDate,string endDate)
        {
            string quyu = "";
            if (!string.IsNullOrWhiteSpace(areaId))
            {
                quyu = "and d.new_quyu in (" + areaId + ")";
            }
            string strSql = "select " +
              " DATEADD(HOUR,8,ny.CreatedOn) as CreatedOn,"+
             "  case ny.new_bizhong " +
                "when '100000000' then '美金'" +
                "when '100000001' then '欧元'" +
                "when '100000002' then '港币'" +
                "when '100000003' then '人民币'else '' end as bizhongzhongwen ," +
                "d.Name as ordername, " +
                "sy.FullName as ownerid, " +
                "tt.new_loaddata  as time , " +
                "pr.name  as chanpin," +
              "  Round(sum(tt.出运重量),2) as cyzhongliang," +
             "  sum(tt.出运重量)-sum(ny.new_zhongliang) as zhongliang," +
            "   Round(sum(tt.利润总额差)-sum(ny.new_profites),2) as zongchae," +
            "   Round(tt.单吨利润差-ny.new_yjlr,2) as yjlr," +
            "   Round((tt.单吨购物成本差-ny.new_totalpurchasecost)*sum(ny.new_zhongliang),2) as costTotaldifference," +
            "   Round(tt.单吨购物成本差-ny.new_totalpurchasecost,2) as totalpurchasecost," +
            "   Round( tt.单吨货物采购价差-ny.new_procurementofgoodsg,2) as procurementofgoodsg," +
            "   Round( tt.单吨货物包装费差-ny.new_packingchargeg,2) as packingchargeg," +
            "   Round( tt.单吨退税差- ny.new_drawback,2) as drawback," +
            "   Round( (tt.单吨物流费用差-ny.new_totallogisticscosts)*sum(ny.new_zhongliang),2) as logisticsTotaldifference," +
            "   Round(tt.单吨物流费用差-ny.new_totallogisticscosts,2) as totallogisticscosts," +
            "   Round(tt.单吨国内运费差-ny.new_domesticfreightq,2) as domesticfreightq," +
            "   Round(tt.单吨海运费差-ny.new_oceanfreight,2) as oceanfreight," +
            "   Round(tt.单吨仓储费差-ny.new_cangkuf,2) as cangkuf," +
            "   Round(tt.单吨包干费差-ny.new_packingcharge,2) as new_packingcharge," +
            "   Round(tt.单吨打托费用差- ny.new_nursery,2) as nursery," +
            "   Round((tt.单吨销售费用差-ny.new_totalcostofsales) *sum(ny.new_zhongliang),2) as salesTotaldifference," +
            "   Round(tt.单吨销售费用差-ny.new_totalcostofsales,2) as totalcostofsales," +
            "   Round(tt.单吨业务费差-ny.new_businesspromotionfee,2) as businesspromotionfee," +
            "   Round(tt.单吨差旅费差-ny.new_travelexpenses,2) as travelexpenses," +
            "   Round(tt.单吨佣金差-ny.new_commission,2) as commission," +
            "   Round(tt.单吨其他费用差 - ny.new_otherexpenses,2) as otherexpenses," +
            "   Round(tt.单吨清关费差-ny.new_qinggf,2) as qinggf," +
            "   Round(tt.单吨关税差 -ny.new_guanshui,2) as guanshui," +
            "   Round((tt.单吨税金保险差-ny.new_taxandinsurancesubtotal)*sum(ny.new_zhongliang),2) as taxesTotaldifference," +
            "   Round(tt.单吨税金保险差-ny.new_taxandinsurancesubtotal,2) as taxandinsurancesubtotal," +
            "   Round(tt.单吨保费差-ny.new_MT,2) as mt ," +
            "   Round(tt.单吨差价税差-ny.new_cjs,2) as cjs," +
            "   Round((tt.单吨财务费用差 - ny.new_financialexpensessubtotal) *sum(ny.new_zhongliang), 2) as financeTotaldifference," +
            "   Round(tt.单吨财务费用差-ny.new_financialexpensessubtotal,2) as financialexpensessubtotal," +
            "   Round(tt.单吨贷款利息差-ny.new_interestonloanfunds,2) as interestonloanfunds," +
            "   Round(tt.单吨税款利息差-ny.new_interestontaxrefundfunds,2) as interestontaxrefundfunds," +
            "   Round(tt.单吨报价差-ny.new_xsbj,2) as xsbj," +
            "    Round(sum(tt.汇率 - ny.new_yjjhhl),2) as exchange," +
            "    Round(sum((tt.汇率 - ny.new_yjjhhl) * tt.报价总额外币),2) as  exchangeTotaldifference" +
            "    from SalesOrderBase(nolock) as d" +
            "     inner join new_ygcb  ny on d.SalesOrderId =ny.new_case" +
            "     inner join SystemUserBase as sy on sy.SystemUserId=d.OwnerId" +
            "     inner join(" +
            "          select c.new_loaddata, ygcb.new_case, c.new_chanpin as 出运产品id , ygcb.new_chanpin as 报价产品id," +
            "          SUM(c.new_profites) as 利润总额差, "+
            "          SUM(c.new_zhongliang) as 出运重量," +
            "          SUM(c.new_yjlr* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨利润差," +
            "          SUM(c.new_totalpurchasecost* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨购物成本差," +
            "          SUM(c.new_procurementofgoodsg* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨货物采购价差," +
            "          SUM(c.new_packingchargeg* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨货物包装费差," +
            "          SUM(c.new_drawback* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨退税差," +
            "          SUM(c.new_totallogisticscosts* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨物流费用差," +
            "          SUM(c.new_domesticfreightq* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨国内运费差," +
            "          SUM(c.new_oceanfreight* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨海运费差," +
            "          SUM(c.new_cangkuf* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨仓储费差," +
            "          SUM(c.new_packingcharge* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨包干费差," +
            "          SUM(c.new_nursery* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨打托费用差," +
            "          SUM(c.new_totalcostofsales* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨销售费用差," +
            "          SUM(c.new_businesspromotionfee* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨业务费差," +
            "          SUM(c.new_travelexpenses* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨差旅费差," +
            "          SUM(c.new_commission* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨佣金差," +
            "          SUM(c.new_otherexpenses* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨其他费用差," +
            "          SUM(c.new_qinggf* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨清关费差," +
            "          SUM(c.new_guanshui* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨关税差," +
            "          SUM(c.new_taxandinsurancesubtotal* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨税金保险差," +
            "          SUM(c.new_MT* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨保费差," +
            "          SUM(c.new_cjs* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨差价税差," +
            "          SUM(c.new_financialexpensessubtotal* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨财务费用差," +
            "          SUM(c.new_interestonloanfunds* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨贷款利息差," +
            "          SUM(c.new_interestontaxrefundfunds* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨税款利息差," +
            "          SUM(c.new_xsbj* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨报价差," +
            "          c.new_yjjhhl as 汇率," +
            "          sum(c.new_usdcost) as 报价总额外币" +
            "         from  new_ygcb(nolock) as ygcb" +
            "         inner join new_chengbenBase(nolock) c on ygcb.new_chanpin=c.new_chanpin and  ygcb.new_case=c.new_ordercode" +
            "          /**where**/" +
            "          group by  ygcb.new_case, c.new_chanpin, ygcb.new_chanpin,c.new_yjjhhl,c.new_loaddata" +
            "      )tt on ny.new_chanpin=tt.报价产品id and   tt.new_case=ny.new_case" +
            "     left join Product(nolock) pr on ny.new_chanpin=pr.Productid" +
            "     where  d.CreatedOn>'2019/1/1'" + quyu +
            "      group by  d.SalesOrderId ,pr.name,d.Name,sy.FullName," +
            "        ny.new_cost," +
            "        tt.单吨利润差,ny.new_yjlr," +
            "      tt.单吨购物成本差,ny.new_totalpurchasecost," +
            "     tt.单吨货物采购价差,ny.new_procurementofgoodsg," +
            "      tt.单吨货物包装费差,ny.new_packingchargeg," +
            "      tt.单吨退税差,ny.new_drawback,tt.new_loaddata," +
            "       tt.单吨物流费用差,ny.new_totallogisticscosts ," +
            "      tt.单吨国内运费差,ny.new_domesticfreightq," +
            "     tt.单吨海运费差,ny.new_oceanfreight," +
            "    tt.单吨仓储费差,ny.new_cangkuf," +
            "   tt.单吨包干费差,ny.new_packingcharge," +
            "   tt.单吨打托费用差,ny.new_nursery," +
            "   tt.单吨销售费用差,ny.new_totalcostofsales," +
            "   tt.单吨业务费差,ny.new_businesspromotionfee," +
            "   tt.单吨差旅费差,ny.new_travelexpenses," +
            "   tt.单吨佣金差,ny.new_commission," +
            "   tt.单吨其他费用差,ny.new_otherexpenses," +
            "   tt.单吨清关费差,ny.new_qinggf," +
            "   tt.单吨关税差,ny.new_guanshui," +
            "   tt.单吨税金保险差,ny.new_taxandinsurancesubtotal," +
            "   tt.单吨保费差,ny.new_MT," +
            "   tt.单吨差价税差,ny.new_cjs," +
            "   tt.单吨财务费用差,ny.new_financialexpensessubtotal," +
            "   tt.单吨贷款利息差,ny.new_interestonloanfunds," +
            "   tt.单吨税款利息差,ny.new_interestontaxrefundfunds," +
            "   tt.单吨报价差,ny.new_xsbj,ny.new_bizhong,ny.CreatedOn";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(strSql);
            //if (!string.IsNullOrWhiteSpace(productId))
            //{
            //    sqlbuilder.Where("ygcb.new_chanpin=@productId", new { productId = productId });
            //}
            //if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
            //{
            //    sqlbuilder.Where("(DATEADD(HOUR, 8, ygcb.CreatedOn) <=  @startDate and @endDate >= DATEADD(HOUR, 8, ygcb.CreatedOn))", new { startDate = startDate, endDate = endDate });
            //    //sqlbuilder.Where("ygcb.CreatedOn between @startDate and @endDate", new { startDate = startDate, endDate = endDate });
            //}

            if (!string.IsNullOrWhiteSpace(productId))
            {
                string productparm = @"select distinct convert(varchar(36),prd.ProductId) ProductId from ProductBase as prd
                inner join Product as pr1 on pr1.ProductId = prd.ParentProductId
                inner join Product as pr2 on pr2.ProductId = pr1.ParentProductId
                where pr2.ProductId = '" + productId+ "'";
                sqlbuilder.Where("ygcb.new_chanpin in("+productparm +")");
            }
            if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
            {
                sqlbuilder.Where("DATEADD(HOUR, 8, c.new_loaddata) between  @startDate and @endDate", new { startDate = startDate, endDate = endDate });
            }
            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                var comparsion = conn.Query<QuoteComparsionModel>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                if (comparsion != null && comparsion.Count() > 0)
                {
                    return comparsion;
                }
                return null;

            };
        }


        /// <summary>
        /// 查询订单区域
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="attributename"></param>
        /// <returns></returns>
        public string GetAllAreaData(string entity, string attributename)
        {
            string jsonArea = "";
            string strSql = @"select distinct sm.Value,sm.AttributeValue  from StringMap as sm
                                left join Entity as e on sm.ObjectTypecode=e.ObjectTypeCode
                                where e.LogicalName='"+ entity + "' and sm.AttributeName='"+ attributename + "'";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(strSql);
            using (IDbConnection dbconn = new SqlConnection(connectionString))
            {
                var result = dbconn.Query<AreaModel>(sqlTemplate.RawSql);
                if (result != null)
                {
                    jsonArea = JsonConvert.SerializeObject(result);
                }
            }
            return jsonArea;
        }

        //查询父类产品
        public string GetAllProductData()
        {
            string jsonProduct = "";
            //string strSql = @"select distinct  convert(varchar(36),pr.ProductId) as ProductId,pr.Name from ProductBase as pr
            //                   inner join new_ygcb as yg on yg.new_chanpin=pr.ProductId";

            string strSql = @"select distinct  convert(varchar(36),pr2.ProductId) as ProductId,pr2.Name from ProductBase as prd
inner join Product as pr1 on pr1.ProductId=prd.ParentProductId
inner join Product as pr2 on pr2.ProductId = pr1.ParentProductId";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(strSql);
            using (IDbConnection dbconn = new SqlConnection(connectionString))
            {
                var result = dbconn.Query<ProductModel>(sqlTemplate.RawSql);
                if (result != null)
                {
                    jsonProduct = JsonConvert.SerializeObject(result);
                }
            }
            return jsonProduct;
        }

    

        /// <summary>
        /// 购物成本总差额
        /// </summary>
        /// <param name="areaId"></param>
        /// <param name="productId"></param>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <returns></returns>
        public string getSumCostTotaldifference(string areaId, string productId, string startDate, string endDate)
        {
            string jsonProduct = "0";
            //string quyu = "";
            //if (!string.IsNullOrWhiteSpace(areaId))
            //{
            //    quyu = "and d.new_quyu in (" + areaId + ")";
            //}
            //string strSql = "select  " +
            //                "  Round(sum(Round(tt.利润总额差,2)-Round(ny.new_profites,2)),2) as sumcost," +
            //                "  Round(sum((Round(tt.单吨购物成本差,2)-Round(ny.new_totalpurchasecost,2))*ny.new_zhongliang),2) as sumcostTotaldifference," +
            //                "  Round(sum((Round(tt.单吨物流费用差,2)-Round(ny.new_totallogisticscosts,2))*ny.new_zhongliang),2) as sumlogisticsTotaldifference," +
            //                "  Round(sum((Round(tt.单吨销售费用差,2)-Round(ny.new_totalcostofsales,2)) *ny.new_zhongliang),2) as sumsalesTotaldifference," +
            //                "  Round(sum((Round(tt.单吨税金保险差,2)-Round(ny.new_taxandinsurancesubtotal,2))*ny.new_zhongliang),2) as sumtaxesTotaldifference," +
            //                "  Round(sum((Round(tt.单吨财务费用差,2)-Round(ny.new_financialexpensessubtotal,2))*ny.new_zhongliang),2) as sumfinanceTotaldifference," +
            //                "  Round(sum((Round(tt.汇率,2) - ny.ExchangeRate) * Round(tt.报价总额外币,2)),2) as  sumexchangeTotaldifference" +
            //                "  from SalesOrderBase(nolock) as d" +
            //                " inner join   new_ygcb ny on d.SalesOrderId = ny.new_case" +
            //                " inner join SystemUserBase as sy on sy.SystemUserId = d.OwnerId" +
            //                " inner join(" +
            //                " select ygcb.new_zhongliang, ygcb.new_case, c.new_chanpin as 出运产品id ,ygcb.new_chanpin as 报价产品id,     " +
            //                "  SUM(c.new_profites) as 利润总额差," +
            //                " SUM(c.new_totalpurchasecost * c.new_zhongliang) / NULLIF(sum(c.new_zhongliang), (0)) as 单吨购物成本差," +
            //                " SUM(c.new_totallogisticscosts * c.new_zhongliang) / NULLIF(sum(c.new_zhongliang), (0)) as 单吨物流费用差," +
            //                " SUM(c.new_totalcostofsales * c.new_zhongliang) / NULLIF(sum(c.new_zhongliang), (0)) as 单吨销售费用差," +
            //                " SUM(c.new_taxandinsurancesubtotal * c.new_zhongliang) / NULLIF(sum(c.new_zhongliang), (0)) as 单吨税金保险差," +
            //                " SUM(c.new_financialexpensessubtotal * c.new_zhongliang) / NULLIF(sum(c.new_zhongliang), (0)) as 单吨财务费用差," +
            //                " c.new_yjjhhl as 汇率,  sum(c.new_usdcost) as 报价总额外币" +
            //                " from new_ygcb (nolock) as ygcb inner join new_chengbenBase(nolock) c on" +
            //                " ygcb.new_chanpin = c.new_chanpin  and ygcb.new_case = c.new_ordercode" +
            //                 "   /**where**/" +
            //                " group by  ygcb.new_case,c.new_chanpin,ygcb.new_chanpin ,ygcb.new_zhongliang,c.new_yjjhhl" +
            //                ")tt" +
            //                " on ny.new_chanpin = tt.报价产品id  and tt.new_case = ny.new_case" +
            //                " left join Product(nolock) pr on ny.new_chanpin = pr.Productid" +
            //               "  where d.CreatedOn>'2019/1/1'" + quyu;
            //SqlBuilder sqlbuilder = new SqlBuilder();
            //var sqlTemplate = sqlbuilder.AddTemplate(strSql);
            //if (!string.IsNullOrWhiteSpace(productId))
            //{
            //    string productparm = @"select distinct convert(varchar(36),prd.ProductId) ProductId from ProductBase as prd
            //    inner join Product as pr1 on pr1.ProductId = prd.ParentProductId
            //    inner join Product as pr2 on pr2.ProductId = pr1.ParentProductId
            //    where pr2.ProductId = '" + productId + "'";
            //    sqlbuilder.Where("ygcb.new_chanpin in(" + productparm + ")");
            //}
            //if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
            //{
            //    sqlbuilder.Where("DATEADD(HOUR, 8, c.new_loaddata) between  @startDate and @endDate", new { startDate = startDate, endDate = endDate });
            //}


            string quyu = "";
            if (!string.IsNullOrWhiteSpace(areaId))
            {
                quyu = "and d.new_quyu in (" + areaId + ")";
            }
            string strSql = "select " +
                " sum(cb.zongchae) as sumcost," +
                " sum(cb.costTotaldifference) as sumcostTotaldifference," +
                " sum(cb.logisticsTotaldifference) as sumlogisticsTotaldifference," +
                " sum(cb.salesTotaldifference) as sumsalesTotaldifference," +
                " sum(cb.taxesTotaldifference) as sumtaxesTotaldifference," +
                " sum(cb.financeTotaldifference) as sumfinanceTotaldifference," +
                " sum(cb.exchangeTotaldifference) as sumexchangeTotaldifference" +
                " from (" +
                " select " +
              "  DATEADD(HOUR,8,ny.CreatedOn) as CreatedOn," +
             "   case ny.new_bizhong " +
                " when '100000000' then '美金'" +
                " when '100000001' then '欧元'" +
                " when '100000002' then '港币'" +
                " when '100000003' then '人民币'else '' end as bizhongzhongwen ," +
                "d.Name as ordername, " +
                "sy.FullName as ownerid, " +
                "tt.new_loaddata  as time , " +
                "pr.name  as chanpin," +
              "  Round(sum(tt.出运重量),2) as cyzhongliang," +
             "  sum(tt.出运重量)-sum(ny.new_zhongliang) as zhongliang," +
            "   Round(sum(tt.利润总额差)-sum(ny.new_profites),2) as zongchae," +
            "   Round(tt.单吨利润差-ny.new_yjlr,2) as yjlr," +
            "   Round((tt.单吨购物成本差-ny.new_totalpurchasecost)*sum(ny.new_zhongliang),2) as costTotaldifference," +
            "   Round(tt.单吨购物成本差-ny.new_totalpurchasecost,2) as totalpurchasecost," +
            "   Round( tt.单吨货物采购价差-ny.new_procurementofgoodsg,2) as procurementofgoodsg," +
            "   Round( tt.单吨货物包装费差-ny.new_packingchargeg,2) as packingchargeg," +
            "   Round( tt.单吨退税差- ny.new_drawback,2) as drawback," +
            "   Round( (tt.单吨物流费用差-ny.new_totallogisticscosts)*sum(ny.new_zhongliang),2) as logisticsTotaldifference," +
            "   Round(tt.单吨物流费用差-ny.new_totallogisticscosts,2) as totallogisticscosts," +
            "   Round(tt.单吨国内运费差-ny.new_domesticfreightq,2) as domesticfreightq," +
            "   Round(tt.单吨海运费差-ny.new_oceanfreight,2) as oceanfreight," +
            "   Round(tt.单吨仓储费差-ny.new_cangkuf,2) as cangkuf," +
            "   Round(tt.单吨包干费差-ny.new_packingcharge,2) as new_packingcharge," +
            "   Round(tt.单吨打托费用差- ny.new_nursery,2) as nursery," +
            "   Round((tt.单吨销售费用差-ny.new_totalcostofsales) *sum(ny.new_zhongliang),2) as salesTotaldifference," +
            "   Round(tt.单吨销售费用差-ny.new_totalcostofsales,2) as totalcostofsales," +
            "   Round(tt.单吨业务费差-ny.new_businesspromotionfee,2) as businesspromotionfee," +
            "   Round(tt.单吨差旅费差-ny.new_travelexpenses,2) as travelexpenses," +
            "   Round(tt.单吨佣金差-ny.new_commission,2) as commission," +
            "   Round(tt.单吨其他费用差 - ny.new_otherexpenses,2) as otherexpenses," +
            "   Round(tt.单吨清关费差-ny.new_qinggf,2) as qinggf," +
            "   Round(tt.单吨关税差 -ny.new_guanshui,2) as guanshui," +
            "   Round((tt.单吨税金保险差-ny.new_taxandinsurancesubtotal)*sum(ny.new_zhongliang),2) as taxesTotaldifference," +
            "   Round(tt.单吨税金保险差-ny.new_taxandinsurancesubtotal,2) as taxandinsurancesubtotal," +
            "   Round(tt.单吨保费差-ny.new_MT,2) as mt ," +
            "   Round(tt.单吨差价税差-ny.new_cjs,2) as cjs," +
            "   Round((tt.单吨财务费用差 - ny.new_financialexpensessubtotal) *sum(ny.new_zhongliang), 2) as financeTotaldifference," +
            "   Round(tt.单吨财务费用差-ny.new_financialexpensessubtotal,2) as financialexpensessubtotal," +
            "   Round(tt.单吨贷款利息差-ny.new_interestonloanfunds,2) as interestonloanfunds," +
            "   Round(tt.单吨税款利息差-ny.new_interestontaxrefundfunds,2) as interestontaxrefundfunds," +
            "   Round(tt.单吨报价差-ny.new_xsbj,2) as xsbj," +
            "    Round(sum(tt.汇率 - ny.new_yjjhhl),2) as exchange," +
            "    Round(sum((tt.汇率 - ny.new_yjjhhl) * tt.报价总额外币),2) as  exchangeTotaldifference" +
            "    from SalesOrderBase(nolock) as d" +
            "     inner join new_ygcb  ny on d.SalesOrderId =ny.new_case" +
            "     inner join SystemUserBase as sy on sy.SystemUserId=d.OwnerId" +
            "     inner join(" +
            "          select c.new_loaddata, ygcb.new_case, c.new_chanpin as 出运产品id , ygcb.new_chanpin as 报价产品id," +
            "          SUM(c.new_profites) as 利润总额差, " +
            "          SUM(c.new_zhongliang) as 出运重量," +
            "          SUM(c.new_yjlr* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨利润差," +
            "          SUM(c.new_totalpurchasecost* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨购物成本差," +
            "          SUM(c.new_procurementofgoodsg* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨货物采购价差," +
            "          SUM(c.new_packingchargeg* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨货物包装费差," +
            "          SUM(c.new_drawback* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨退税差," +
            "          SUM(c.new_totallogisticscosts* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨物流费用差," +
            "          SUM(c.new_domesticfreightq* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨国内运费差," +
            "          SUM(c.new_oceanfreight* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨海运费差," +
            "          SUM(c.new_cangkuf* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨仓储费差," +
            "          SUM(c.new_packingcharge* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨包干费差," +
            "          SUM(c.new_nursery* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨打托费用差," +
            "          SUM(c.new_totalcostofsales* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨销售费用差," +
            "          SUM(c.new_businesspromotionfee* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨业务费差," +
            "          SUM(c.new_travelexpenses* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨差旅费差," +
            "          SUM(c.new_commission* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨佣金差," +
            "          SUM(c.new_otherexpenses* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨其他费用差," +
            "          SUM(c.new_qinggf* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨清关费差," +
            "          SUM(c.new_guanshui* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨关税差," +
            "          SUM(c.new_taxandinsurancesubtotal* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨税金保险差," +
            "          SUM(c.new_MT* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨保费差," +
            "          SUM(c.new_cjs* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨差价税差," +
            "          SUM(c.new_financialexpensessubtotal* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨财务费用差," +
            "          SUM(c.new_interestonloanfunds* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨贷款利息差," +
            "          SUM(c.new_interestontaxrefundfunds* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨税款利息差," +
            "          SUM(c.new_xsbj* c.new_zhongliang)/NULLIF(sum(c.new_zhongliang), (0)) as 单吨报价差," +
            "          c.new_yjjhhl as 汇率," +
            "          sum(c.new_usdcost) as 报价总额外币" +
            "         from  new_ygcb(nolock) as ygcb" +
            "         inner join new_chengbenBase(nolock) c on ygcb.new_chanpin=c.new_chanpin and  ygcb.new_case=c.new_ordercode" +
            "          /**where**/" +
            "          group by  ygcb.new_case, c.new_chanpin, ygcb.new_chanpin,c.new_yjjhhl,c.new_loaddata" +
            "      )tt on ny.new_chanpin=tt.报价产品id and   tt.new_case=ny.new_case" +
            "     left join Product(nolock) pr on ny.new_chanpin=pr.Productid" +
            "     where  d.CreatedOn>'2019/1/1'" + quyu +
            "      group by  d.SalesOrderId ,pr.name,d.Name,sy.FullName," +
            "        ny.new_cost," +
            "        tt.单吨利润差,ny.new_yjlr," +
            "      tt.单吨购物成本差,ny.new_totalpurchasecost," +
            "     tt.单吨货物采购价差,ny.new_procurementofgoodsg," +
            "      tt.单吨货物包装费差,ny.new_packingchargeg," +
            "      tt.单吨退税差,ny.new_drawback,tt.new_loaddata," +
            "       tt.单吨物流费用差,ny.new_totallogisticscosts ," +
            "      tt.单吨国内运费差,ny.new_domesticfreightq," +
            "     tt.单吨海运费差,ny.new_oceanfreight," +
            "    tt.单吨仓储费差,ny.new_cangkuf," +
            "   tt.单吨包干费差,ny.new_packingcharge," +
            "   tt.单吨打托费用差,ny.new_nursery," +
            "   tt.单吨销售费用差,ny.new_totalcostofsales," +
            "   tt.单吨业务费差,ny.new_businesspromotionfee," +
            "   tt.单吨差旅费差,ny.new_travelexpenses," +
            "   tt.单吨佣金差,ny.new_commission," +
            "   tt.单吨其他费用差,ny.new_otherexpenses," +
            "   tt.单吨清关费差,ny.new_qinggf," +
            "   tt.单吨关税差,ny.new_guanshui," +
            "   tt.单吨税金保险差,ny.new_taxandinsurancesubtotal," +
            "   tt.单吨保费差,ny.new_MT," +
            "   tt.单吨差价税差,ny.new_cjs," +
            "   tt.单吨财务费用差,ny.new_financialexpensessubtotal," +
            "   tt.单吨贷款利息差,ny.new_interestonloanfunds," +
            "   tt.单吨税款利息差,ny.new_interestontaxrefundfunds," +
            "   tt.单吨报价差,ny.new_xsbj,ny.new_bizhong,ny.CreatedOn" +
            ")cb";
            SqlBuilder sqlbuilder = new SqlBuilder();
            var sqlTemplate = sqlbuilder.AddTemplate(strSql);
            if (!string.IsNullOrWhiteSpace(productId))
            {
                string productparm = @"select distinct convert(varchar(36),prd.ProductId) ProductId from ProductBase as prd
                inner join Product as pr1 on pr1.ProductId = prd.ParentProductId
                inner join Product as pr2 on pr2.ProductId = pr1.ParentProductId
                where pr2.ProductId = '" + productId + "'";
                sqlbuilder.Where("ygcb.new_chanpin in(" + productparm + ")");
            }
            if (!string.IsNullOrWhiteSpace(startDate) && !string.IsNullOrWhiteSpace(endDate))
            {
                sqlbuilder.Where("DATEADD(HOUR, 8, c.new_loaddata) between  @startDate and @endDate", new { startDate = startDate, endDate = endDate });
            }


            using (IDbConnection conn = new SqlConnection(connectionString))
            {
                var mpclass = conn.Query<CostModel>(sqlTemplate.RawSql, sqlTemplate.Parameters);
                if (mpclass != null && mpclass.Count() > 0)
                {
                    jsonProduct = JsonConvert.SerializeObject(mpclass);
                }
            };
            return jsonProduct;
        }
        


        public string getQuery(string InputAreaId, string InputProduct, string InpuSart, string InputEnd)
        {
            List<QuoteComparsionModel> listq = new List<QuoteComparsionModel>();
            var jsonClass = "";
            IEnumerable<dynamic> result = GetQuoteData(InputAreaId, InputProduct, InpuSart, InputEnd);
            if (result != null)
            {
                //result.ToList().ForEach(item => {
                //    QuoteComparsionModel comparsionModel = new QuoteComparsionModel();
                //    comparsionModel.ordernumber = item.ordernumber;
                //    listq.Add(comparsionModel);
                //});
                //if (listq.Count>0)
                //{
                //    jsonClass = JsonConvert.SerializeObject(listq);
                //}

                jsonClass = JsonConvert.SerializeObject(result);
            }
            return jsonClass;
        }




    }
}